﻿
using System;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Windows.Forms;


namespace CmdPrep.Backends
{
    static class DbfBackend
    {       
        #region Private static methods
        
        private static bool validateDbfFileInfo(FileInfo dbfFileInfo, bool showMessageBoxOnFailure)
        {
            if (! dbfFileInfo.Extension.Equals(".dbf", StringComparison.OrdinalIgnoreCase))
            {
                if (showMessageBoxOnFailure)
                    MessageBox.Show(
                        "Not an DBF file : '" + dbfFileInfo.FullName + "'", 
                        "CmdPrep - DBF file not found",
                        MessageBoxButtons.OK, 
                        MessageBoxIcon.Warning);
                
              return false;
            }
                           
            if (! dbfFileInfo.Exists)
            {
                if (showMessageBoxOnFailure)
                    MessageBox.Show(
                        "Unresolved file path : '" + dbfFileInfo.FullName + "'", 
                        "CmdPrep - DBF file not found",
                        MessageBoxButtons.OK, 
                        MessageBoxIcon.Warning);
                
                return false;
            }
        
            return true;
        }
        
        private static string getConnectionString(FileInfo dbfFileInfo)
        {
            // Sugar
            string folder     = dbfFileInfo.DirectoryName;
            string file       = dbfFileInfo.Name;
            string extension  = dbfFileInfo.Extension;
            
            // Connection string
            return @"Driver={Microsoft dBASE Driver (*" + extension + ")};DSN=" + file + ";DriverID=277;DBQ=" + folder;
        }
        
        private static bool openOdbcConnection(OdbcConnection dbConn)
        {
            try 
            {
                if (dbConn != null)
                    dbConn.Open();
            }
            catch (System.Exception e) 
            {                
                MessageBox.Show(
                    "Exception caught opening DBF connection : " + e.Message, 
                    "CmdPrep - DBF connection could not open",
                    MessageBoxButtons.OK, 
                    MessageBoxIcon.Warning);
            }
            
            return (dbConn != null) && (dbConn.State == ConnectionState.Open);
        }
        
        #endregion
        
        #region Public static methods
                        
        public static DataTable getProjectsDataTable(FileInfo dbfFileInfo) 
        {
            if (! validateDbfFileInfo(dbfFileInfo, true /* show message box on failure */ ))
                return null;
            
            string connectionString   = getConnectionString(dbfFileInfo);
            if (string.IsNullOrEmpty(connectionString))
                return null;
            
            OdbcConnection dbConn     = new OdbcConnection(connectionString);
            if (! openOdbcConnection(dbConn))
                return null;
            
            OdbcCommand dbComm        = dbConn.CreateCommand();
            dbComm.CommandText = "Select PNAME, ALW, ADIR, QLW, QDIR, ZLW, ZDIR from PROJECTS where FA_NAME NOT like 'cadett%' order by PNAME asc";
            
            DataTable projectsDataTable = new DataTable();
            projectsDataTable.Load(dbComm.ExecuteReader());
            
            dbConn.Close();
            dbComm.Dispose();
            dbConn.Dispose();
            
            return projectsDataTable;
        }
        
        #endregion
    }
}
